
###### INITIALIZE
#setwd("C:/Users/Philipp/Projects/geomatch")
setwd("C:/Users/spadmin/PhD/Projects/EPREmergence/Stats/geomatch")
source("R/PGHandlerFunctions_20131001.R")
source("R/GeoDBMover_v1.0.R")
source("R/PGHandlerFunctions_20131001.R")
library(sp)

###### CONNECT TO DB
con <- getPGConn("growup", 5432, "cederman.ethz.ch", "admin", "hNo7Yoo")





t.res <- dbSendQuery(con, statement=paste("CREATE TABLE hunzikp.drawcountries AS SELECT distinct cyse.countries_cowid, gcp.polygons 
                                          FROM rbe.countryyearsocioecon cyse
                                          LEFT JOIN hunzikp.geocountryperiods_v2 gcp on gcp.countries_cowid = cyse.countries_cowid AND gcp.enddate = '2008-06-30'
                                          WHERE cyse.year = 2009 AND (cyse.ssafrica = 1 OR cyse.asia = 1);"))
countries.spdf <- dbReadSpatial(con, schemaname="hunzikp", tablename="drawcountries", geomcol= "polygons", idcol="countries_cowid")
t.res <- dbSendQuery(con, statement=paste("DROP TABLE IF EXISTS hunzikp.drawcountries;"))


t.res <- dbSendQuery(con, statement=paste("CREATE TABLE hunzikp.drawgroups AS SELECT distinct cyse.countries_cowid, gyp.cowgroupid, ggp.polygons 
                                          FROM rbe.countryyearsocioecon cyse
                                          LEFT JOIN rbe.groupyearpolitics gyp ON gyp.countries_cowid = cyse.countries_cowid AND gyp.year = 2009 AND gyp.isrelevant = 1
                                          INNER JOIN hunzikp.geogroupperiods_v2 ggp ON ggp.groups_cowgroupid = gyp.cowgroupid AND ggp.enddate = '2009-12-31' AND ggp.geotype_typeid IN (1,3,6)
                                          WHERE cyse.year = 2009 AND (cyse.ssafrica = 1 OR cyse.asia = 1);"))
groups.spdf <- dbReadSpatial(con, schemaname="hunzikp", tablename="drawgroups", geomcol= "polygons", idcol="cowgroupid")
t.res <- dbSendQuery(con, statement=paste("DROP TABLE IF EXISTS hunzikp.drawgroups;"))




colors <- rainbow(nrow(groups.spdf))
groups.spdf$col <- sample(colors)
pdf("Plots/epr.pdf")
par(mar=c(0,0,0,0))
plot(countries.spdf[countries.spdf$countries_cowid != 950,], col="grey", border="grey36")
plot(groups.spdf[groups.spdf$countries_cowid != 950,], col=groups.spdf$col[groups.spdf$countries_cowid != 950], border=NA, add=TRUE)
dev.off()